#! /bin/bash

/export/server/presto/bin/presto --server hadoop01:8090 --catalog hive --schema default --execute \
" \
insert into hive.dws.day_sign \
select create_date_time, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 23 then null \
           else if(origin_type = 'NETSERVICE' or origin_type = 'PRESIGNUP', '线上', '线下') \
           end                          as origin_type, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 23 then itcast_school_id \
           when 7 then itcast_school_id \
           when 3 then itcast_school_id \
           else null \
           end                          as it_school_id, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 23 then itcast_school_name \
           when 7 then itcast_school_name \
           when 3 then itcast_school_name \
           else null \
           end                          as itcast_school_name, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 11 then itcast_subject_id \
           when 3 then itcast_subject_id \
           else null \
           end                          as itcast_subject_id, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 11 then itcast_subject_name \
           when 3 then itcast_subject_name \
           else null \
           end                          as itcast_subject_name, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 13 then origin_channel \
           else null \
           end                          as origin_channel, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 14 then department_name \
           else null \
           end                          as department_name, \
       sum(if(payment_state = 1, 1, 0)) as pay_count, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 15 then sum(if(id is not null, 1, 0)) \
           else 0 \
           end                          as relationship_count, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 15 then sum(if(appeal_status = 2, 1, 0)) \
           else 0 \
           end                          as valid_count, \
       case grouping(create_date_time, origin_type, itcast_school_id, itcast_subject_id, origin_channel, \
                     department_name) \
           when 23 then 'school' \
           when 7 then 'origin_school' \
           when 11 then 'origin_subject' \
           when 3 then 'origin_school_subject' \
           when 13 then 'origin_type_channel' \
           when 14 then 'origin_department' \
           when 15 then 'origin' \
           else 'other' \
           end                          as group_type \
from hive.dwb.width_sign \
group by grouping sets ( (create_date_time, itcast_school_id, itcast_school_name), \
                         (create_date_time, origin_type, itcast_school_id, itcast_school_name), \
                         (create_date_time, origin_type, itcast_subject_id, itcast_subject_name), \
                         (create_date_time, origin_type, itcast_school_id, itcast_school_name, itcast_subject_id, \
                          itcast_subject_name), \
                         (create_date_time, origin_type, origin_channel), \
                         (create_date_time, origin_type, department_name), \
                         (create_date_time, origin_type) \
    ); \
"